package cn.icanci.mv.dao.impl;


import cn.icanci.mv.dao.ICustomerDao;
import cn.icanci.mv.dao.IOrderDao;
import cn.icanci.mv.entity.Customer;
import cn.icanci.mv.entity.Order;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author: icanci
 * @ProjectName: kangaroo-orm
 * @PackageName: cn.icanci.mv.entity.dao.impl
 * @Date: Created in 2020/8/31 14:44
 * @ClassAction: IOrderDao 顶级接口的实现类
 */
public class OrderDaoImpl implements IOrderDao {

    private static final String URL = "jdbc:mysql:///j08?serverTimezone=UTC";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "root";
    private static Connection connection;
    private static PreparedStatement preparedStatement;
    private static ResultSet resultSet;
    private static ICustomerDao customerDao = new CustomerDaoImpl();

    private static void init() {
        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 连接数据库
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Order> findAll(Integer total, Date date) {
        List<Order> list = new ArrayList<Order>();
        try {
            init();
            if (null == total && null == date) {
                String sql = "select id,ordno,total,create_date,customer_id from tbl_ord";
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    Order order = new Order();
                    order.setId(resultSet.getInt(1));
                    order.setOrdno(resultSet.getInt(2));
                    order.setTotal(resultSet.getInt(3));
                    order.setCreateDate(new Date(resultSet.getDate(4).getTime()));
                    Customer byId = customerDao.findById(resultSet.getInt(5));
                    order.setCustomer(byId);
                    list.add(order);
                }
            } else if (null == date && null != total) {
                // 根据 total 查询
                String sql = "select id,ordno,total,create_date,customer_id from tbl_ord where total = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, total);
                resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    Order order = new Order();
                    order.setId(resultSet.getInt(1));
                    order.setOrdno(resultSet.getInt(2));
                    order.setTotal(resultSet.getInt(3));
                    order.setCreateDate(new Date(resultSet.getDate(4).getTime()));
                    Customer byId = customerDao.findById(resultSet.getInt(5));
                    order.setCustomer(byId);
                    list.add(order);
                }
            } else if (null == total && null != date) {
                // 根据date查询
                // 根据 total 查询
                String sql = "select id,ordno,total,create_date,customer_id from tbl_ord where create_date = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setDate(1, new java.sql.Date(date.getTime()));
                resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    Order order = new Order();
                    order.setId(resultSet.getInt(1));
                    order.setOrdno(resultSet.getInt(2));
                    order.setTotal(resultSet.getInt(3));
                    order.setCreateDate(new Date(resultSet.getDate(4).getTime()));
                    Customer byId = customerDao.findById(resultSet.getInt(5));
                    order.setCustomer(byId);
                    list.add(order);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResources();
        }
        return list;
    }

    @Override
    public List<Order> findAllByCustomerId(Integer customerId) {
        List<Order> list = new ArrayList<>();
        try {
            init();
            String sql = "select id,ordno,total,create_date from tbl_ord where customer_id = ?";
            preparedStatement = connection.prepareStatement(sql);
            OrderDaoImpl.preparedStatement.setInt(1, customerId);
            resultSet = OrderDaoImpl.preparedStatement.executeQuery();
            while (resultSet.next()) {
                Order order = new Order();
                order.setId(resultSet.getInt(1));
                order.setOrdno(resultSet.getInt(2));
                order.setTotal(resultSet.getInt(3));
                order.setCreateDate(new Date(resultSet.getDate(4).getTime()));
                list.add(order);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResources();
        }
        return list;
    }

    @Override
    public void deleteByCustomerId(Integer customerId) {
        try {
            init();
            String deleteOrderSql = "delete from tbl_ord where customer_id = ?";
            preparedStatement = connection.prepareStatement(deleteOrderSql);
            preparedStatement.setInt(1, customerId);
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResources();
        }
    }

    @Override
    public void insertOrder(Order order, Integer customerId) {
        try {
            init();
            String insertOrderSql = "insert into tbl_ord(ordno,total,create_date,customer_id) values(?,?,?,?)";
            preparedStatement = connection.prepareStatement(insertOrderSql);
            preparedStatement.setInt(1, order.getOrdno());
            preparedStatement.setInt(2, order.getTotal());
            preparedStatement.setDate(3, new java.sql.Date(order.getCreateDate().getTime()));
            preparedStatement.setInt(4, customerId);
            preparedStatement.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeResources();
        }
    }


    private static void closeResources() {
        try {
            if (connection != null) {
                connection.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
